<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
require('includes/application_top.php');
global $db;
//process ingredient
if(isset($_POST['ACTION']))
{
    $cmd = $_POST['Command'];    
    $ingredients_id = $_POST['ingredients_id'];    
    $ingredients_ddd = $_POST['ingredients_ddd'];
    $ingredients_unit = $_POST['ingredients_unit'];
    $ingredients_descriptions = $_POST['ingredients_description'];    
    if($cmd === "ADD")
    {
        //insert into ingredients table
        $sql_data_array = array('ingredients_ddd' => $ingredients_ddd,
                                'ingredients_unit' => $ingredients_unit);
        zen_db_perform("ingredients", $sql_data_array);

        $ingredients_id = $db->Insert_ID();
        //insert into ingredients_description table
        foreach($ingredients_descriptions as $description)
        {            
            $sql_descriptions = "insert into ingredients_description(ingredients_id, languages_id, ingredients_description)
                        values (:ingredients_id, :languages_id, :ingredients_description)";        
            $sql_descriptions = $db->bindVars($sql_descriptions, ':ingredients_id', $ingredients_id, 'integer');
            $sql_descriptions = $db->bindVars($sql_descriptions, ':languages_id', $description['languages_id'], 'integer');
            $sql_descriptions = $db->bindVars($sql_descriptions, ':ingredients_description', $description['text'], 'string');
            $db->Execute($sql_descriptions);        
        }
    }
    else if($cmd === "UPDATE")
    {
        //update ingredients table
        $sql_ingredients = "update ingredients
                            set ingredients_ddd=:ingredients_ddd
                                , ingredients_unit=:ingredients_unit
                            where ingredients_id=:ingredients_id";
        $sql_ingredients = $db->bindVars($sql_ingredients, ':ingredients_ddd', $ingredients_ddd, 'float');
        $sql_ingredients = $db->bindVars($sql_ingredients, ':ingredients_unit', $ingredients_unit, 'string');
        $sql_ingredients = $db->bindVars($sql_ingredients, ':ingredients_id', $ingredients_id, 'integer'); 
        $db->Execute($sql_ingredients);        
        //update ingredients_description table        
        foreach($ingredients_descriptions as $description)
        {            
            $sql_descriptions = "update ingredients_description 
                        set ingredients_description=:ingredients_description
                        where ingredients_id=:ingredients_id and languages_id=:languages_id";        
            $sql_descriptions = $db->bindVars($sql_descriptions, ':ingredients_description', $description['text'], 'string');
            $sql_descriptions = $db->bindVars($sql_descriptions, ':ingredients_id', $ingredients_id, 'integer');
            $sql_descriptions = $db->bindVars($sql_descriptions, ':languages_id', $description['languages_id'], 'integer');            
            $db->Execute($sql_descriptions);        
        }
        //recalculate ddd_serving and ddd_100 in products label
        if((float)$ingredients_ddd > 0)            
            $db->Execute("UPDATE products_label SET 
                            products_label_ddd_serving=products_label_qty_serving/$ingredients_ddd,
                            products_label_ddr_100=products_label_qty_100/$ingredients_ddd
                          WHERE ingredients_id='$ingredients_id'");
        else
            $db->Execute("UPDATE products_label SET 
                            products_label_ddd_serving=0,
                            products_label_ddr_100=0
                          WHERE ingredients_id='$ingredients_id'");
    }
    else if($cmd === "DELETE")
    {        
        //delete ingredients table
        $sql_ingredients = "delete from ingredients where ingredients_id=:ingredients_id";
        $sql_ingredients = $db->bindVars($sql_ingredients, ':ingredients_id', $ingredients_id, 'integer');
        $db->Execute($sql_ingredients);        
        //delete ingredients_description table
        $sql_ingredients = "delete from ingredients_description where ingredients_id=:ingredients_id";
        $sql_ingredients = $db->bindVars($sql_ingredients, ':ingredients_id', $ingredients_id, 'integer');
        $db->Execute($sql_ingredients);   
        //delete products label within ingredients
        $db->Execute("delete from products_label where ingredients_id=$ingredients_id");        
    }
}

//get array language expect default language
$languages = $db->Execute("select languages_id, name, code
                           from " . TABLE_LANGUAGES . " 
                           where languages_id <> '" . $_SESSION['languages_id'] . "'
                           order by sort_order");
$languages_array_ingre = array();
while (!$languages->EOF) {
    $languages_array_ingre[] = array('id' => $languages->fields['languages_id'],
                                'name' => $languages->fields['name'],
                                'code' => $languages->fields['code']);
    $languages->MoveNext();
}

//generate ingredients follow default language
$ingredient_listing_default = $db->Execute("SELECT ingredients_id, ingredients_description 
                            FROM ingredients_description
                            WHERE languages_id='" . $_SESSION['languages_id'] . "'
                            ORDER BY ingredients_description");

//generate listing ingredients
$sql_ingredients = "SELECT i.ingredients_id
                , MAX(CASE WHEN id.languages_id = '" . $_SESSION['languages_id'] . "' THEN id.ingredients_description END) AS name
                , i.ingredients_ddd, i.ingredients_unit";
for($i = 0, $n = sizeof($languages_array_ingre); $i < $n; $i++)
{        
    $l = $languages_array_ingre[$i];
    $sql_ingredients .= ", MAX(CASE WHEN id.languages_id = '" . $l['id'] . "' THEN id.ingredients_description END) AS languages_" . $l['code'] . " ";
}
$sql_ingredients .= "FROM ingredients i
                        JOIN ingredients_description id ON i.ingredients_id = id.ingredients_id
                     GROUP BY ingredients_id, i.ingredients_ddd, i.ingredients_unit
                     ORDER BY name";

$listings_ingredient = $db->Execute($sql_ingredients);

?>

<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN">
<html <?php echo HTML_PARAMS; ?>>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET; ?>">
<title><?php echo TITLE; ?></title>
<link rel="stylesheet" type="text/css" href="includes/stylesheet.css">
<link rel="stylesheet" type="text/css" href="includes/cssjsmenuhover.css" media="all" id="hoverJS">
<script language="javascript" src="includes/menu.js"></script>
<script language="javascript" src="includes/general.js"></script>
<script type="text/javascript" src="/jquery-1.7.1.js"></script>
<script type="text/javascript">
  <!--
  function init()
  {
    cssjsmenu('navbar');
    if (document.getElementById)
    {
      var kill = document.getElementById('hoverJS');
      kill.disabled = true;
    }
  }
  // -->
</script>
</head>
<body onload="init()">    
<!-- header //-->
<?php require(DIR_WS_INCLUDES . 'header.php'); ?>
<!-- header_eof //-->

<!-- dropdown ingredients //-->
<?php 
if($ingredient_listing_default->RecordCount() > 0)
{
?>
    <select style="margin-left:150px; margin-right:auto; margin-top: 20px;" id="ddl_ingredients" onchange="selectIngredients()">
<?php
    while(!$ingredient_listing_default->EOF)
    {
        echo "<option value='" . $ingredient_listing_default->fields['ingredients_id'] . "'>" . $ingredient_listing_default->fields['ingredients_description'] . "</option>";
        $ingredient_listing_default->MoveNext();
    }
?>
    </select>
<?php
}
?>
<!-- dropdown ingredients eof //-->

<form id="frm_ingredients" action="ingredients_insertion.php" method="POST">
    <table style="margin-left:auto; margin-right:auto; margin-top: 20px; margin-bottom:30px; width: 80%">
        <tr>
            <td><b>Name</b></td>
            <td><input type="text" id="name" name="ingredients_description[<?php echo $_SESSION['languages_id']; ?>][text]" />
                <input type="hidden" value="<?php echo $_SESSION['languages_id']; ?>" name="ingredients_description[<?php echo $_SESSION['languages_id']; ?>][languages_id]" />
            </td>        
            <td><b>DDD%</b></td>
            <td>
                <input type="text" id="ddd" name="ingredients_ddd" value="" />
            </td>        
            <td><b>Unit</b></td>
            <td>
                <input type="text" id="unit" name="ingredients_unit" value="" />
            </td>        
    <?php 
    for($i = 0, $n = sizeof($languages_array_ingre); $i < $n; $i++)
    {        
        $l = $languages_array_ingre[$i];
        
    ?>        
            <td><b><?php echo $l['name']; ?></b></td>
            <td><input type="text" id="languages<?php echo $i; ?>" name="ingredients_description[<?php echo $l['id']; ?>][text]" />
                <input type="hidden" value="<?php echo $l['id']; ?>" name="ingredients_description[<?php echo $l['id']; ?>][languages_id]" />
            </td>        
    <?php
    }
    ?>        
            <td>
                <input type="submit" id="btnAction" name="ACTION" value="Add" style="width: 75px" />
                <input type="hidden" id="cmd" name="Command" value="ADD" />                
                <input type="hidden" id="id" name="ingredients_id" value="" />
            </td>  
        </tr>
    </table>
</form>

<table style="margin-left:auto; margin-right:auto; margin-top: 20px; margin-bottom:30px; width: 80%">
    <tr class="dataTableHeadingRow">
        <td align="center" class="dataTableHeadingContent">Name</td>
        <td align="center" class="dataTableHeadingContent">DDD%</td>
        <td align="center" class="dataTableHeadingContent">Unit</td>
    <?php 
    for($i = 0, $n = sizeof($languages_array_ingre); $i < $n; $i++)
    {
        $l = $languages_array_ingre[$i];
    ?>
        <td align="center" class="dataTableHeadingContent"><?php echo $l['name']; ?></td>        
    <?php
    }
    ?>
        <td>&nbsp;</td><td>&nbsp;</td>
    </tr>
<?php 
while(!$listings_ingredient->EOF)
{
?>
    <tr class="dataTableRow" id="row_<?php echo $listings_ingredient->fields['ingredients_id'] ?>">
        <td align="center" class="dataTableContent">
            <a name="#<?php echo $listings_ingredient->fields['ingredients_id'] ?>" class="name"><?php echo $listings_ingredient->fields['name']; ?></a></td>
        <td align="right" class="dataTableContent ddd"><?php echo $listings_ingredient->fields['ingredients_ddd']; ?></td>
        <td align="center" class="dataTableContent unit"><?php echo $listings_ingredient->fields['ingredients_unit']; ?></td>
    <?php 
    for($i = 0, $n = sizeof($languages_array_ingre); $i < $n; $i++)
    {
        $l = $languages_array_ingre[$i];
    ?>
        <td align="center" class="dataTableContent languages<?php echo $i; ?>"><?php echo $listings_ingredient->fields['languages_' . $l['code']]; ?></td>        
    <?php
    }
    ?>
        <td align="center" style="width: 45px">
            <input type="button" id="btn_update_<?php echo $listings_ingredient->fields['ingredients_id'] ?>" value="Update" onclick="updateIngredients(<?php echo $listings_ingredient->fields['ingredients_id'] ?>)" /></td>
        <td align="center" style="width: 45px">
            <input type="button" id="btn_update_<?php echo $listings_ingredient->fields['ingredients_id'] ?>" value="Delete" onclick="deleteIngredients(<?php echo $listings_ingredient->fields['ingredients_id'] ?>)" />            
        </td>
    </tr>
<?php
    $listings_ingredient->MoveNext();
}
?>
</table>
<!-- body_eof //-->

<!-- footer //-->
<div class="footer-area">
<?php require(DIR_WS_INCLUDES . 'footer.php'); ?>
</div>
<!-- footer_eof //-->
<br />
</body>
<script type="text/javascript">
    var length_languages = <?php echo sizeof($languages_array_ingre); ?>;
    function updateIngredients(id)
    {
        var row = $('#row_' + id);
        $('#name').val(row.find('.name').html());
        $('#ddd').val(row.find('.ddd').html());
        $('#unit').val(row.find('.unit').html());
        for(var i = 0; i < length_languages; i++)
        {
            $('#languages' + i).val(row.find('.languages' + i).html());
        }
        $('#id').val(id);
        $('#btnAction').val('Update');        
        $('#cmd').val('UPDATE');
        $(document).scrollTop(0,0);
        
    }
    
    function deleteIngredients(id)
    {
        if(confirm("Are you sure to delete this ingredient!"))
        {
            $('#id').val(id);            
            $('#cmd').val('DELETE');
            $('#btnAction').val('Delete'); 
            $('#btnAction').click();
        }
    }
    
    function selectIngredients()
    {
        window.location = "#" + $('#ddl_ingredients').val();
    }
    
    
</script>
</html>
<?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>